package com.spring.common.util;


import com.cscec.financial.bean.finance.pojo.FinLeasePlan;
import com.definesys.mpaas.common.exception.MpaasBusinessException;
import com.definesys.mpaas.query.MpaasQueryFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * wuzhu 导入还款计划表
 */
public class ExcelUtil {

    @Autowired
    private MpaasQueryFactory factory;

    /**
     * 导入物料详情单
     *
     * @param file
     * @return
     */
    public static List<FinLeasePlan> importMrLines(MultipartFile file) {
        if (file.isEmpty()) {
            throw new RuntimeException("【错误信息】：导入文件不能为空！");
        }
        List list = new ArrayList();
        String extName = file.getOriginalFilename()
                .substring(file.getOriginalFilename()
                        .lastIndexOf("."));
        Workbook a_workbook = null;
        InputStream in = null;
        try {
            in = file.getInputStream();

            if (".xlsx".equals(extName)) {
                a_workbook = new XSSFWorkbook(in);
                list = importMrline2007(a_workbook);
            } else if (".xls".equals(extName)) {
                a_workbook = new HSSFWorkbook(in);
                list = importMrline2003(a_workbook);
            } else {
                throw new MpaasBusinessException("文件格式不对！");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return list;
    }


    /**
     * 导入物料详情列表  2017版的
     *
     * @param a_workbook
     * @return
     */
    private static List<FinLeasePlan> importMrline2007(Workbook a_workbook) {
        XSSFWorkbook workbook = (XSSFWorkbook) a_workbook;
        List list = new ArrayList();
        Sheet sheet = workbook.getSheetAt(0);
        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        //第三行进行遍历
        if (maxRow < 1) {
            throw new RuntimeException("表格中至少要有一行有效数据！");
        }
        for (int i = 2; i <= maxRow - 2; i++) {
            Row row = sheet.getRow(i);
            FinLeasePlan finLease = new FinLeasePlan();
            if (row.getCell(0) != null) {
                //期数
                String RepaymentPeriod = row.getCell(0).toString();
                System.out.println("期数" + RepaymentPeriod);
                finLease.setRepaymentPeriod(RepaymentPeriod);
            }
            //租金支付日期
            if (row.getCell(1) != null) {
                Date LeasePayDate = row.getCell(1).getDateCellValue();
                System.out.println("租金支付日期" + LeasePayDate);
                finLease.setLeasePayDate(LeasePayDate);
            }
            //天数
            if (row.getCell(2) != null) {
                String Day = row.getCell(2).toString();
                System.out.println("天数" + Day);
                finLease.setDay(Day);
            }
            //本金
            if (row.getCell(3) != null) {
                String Capital = row.getCell(3).toString();
                System.out.println("本金" + Capital);
                finLease.setCapital(Capital);
            }
            //本金增值税
            if (row.getCell(4) != null) {
                String CapitalVat = row.getCell(4).toString();
                System.out.println("本金增值税" + CapitalVat);
                finLease.setCapitalVat(CapitalVat);
            }
            //利息
            if (row.getCell(5) != null) {
                String Interest = row.getCell(5).toString();
                System.out.println("利息" + Interest);
                finLease.setInterest(Interest);
            }
            //利息增值税
            if (row.getCell(6) != null) {
                String InterestVat = row.getCell(6).toString();
                System.out.println("利息增值税" + InterestVat);
                finLease.setInterestVat(InterestVat);
            }
            //每期租金
            if (row.getCell(7) != null) {
                String LeaseAmt = row.getCell(7).toString();
                System.out.println("每期租金" + LeaseAmt);
                finLease.setLeaseAmt(LeaseAmt);
            }
            list.add(finLease);
        }
        return list;
    }

    /**
     * 导入物料详情列表 2013版的
     *
     * @param a_workbook
     * @return
     */
    private static List<FinLeasePlan> importMrline2003(Workbook a_workbook) {
        HSSFWorkbook workbook = (HSSFWorkbook) a_workbook;
        List list = new ArrayList();
        HSSFSheet sheet = workbook.getSheetAt(0);
        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        if (maxRow < 1) {
            throw new RuntimeException("表格中至少要有一行有效数据！");
        }
        //第3行进行遍历
        for (int i = 2; i <= maxRow - 2; i++) {
            HSSFRow row = sheet.getRow(i);
            FinLeasePlan finLease = new FinLeasePlan();

            if (row.getCell(0) != null) {
                //期数
                String RepaymentPeriod = row.getCell(0).toString();
                System.out.println("期数" + RepaymentPeriod);
                finLease.setRepaymentPeriod(RepaymentPeriod);
            }
            //租金支付日期
            if (row.getCell(1) != null) {
                Date LeasePayDate = row.getCell(1).getDateCellValue();
                System.out.println("租金支付日期" + LeasePayDate);
                finLease.setLeasePayDate(LeasePayDate);
            }
            //天数
            if (row.getCell(2) != null) {
                String Day = row.getCell(2).toString();
                System.out.println("天数" + Day);
                finLease.setDay(Day);
            }
            //本金
            if (row.getCell(3) != null) {
                String Capital = row.getCell(3).toString();
                System.out.println("本金" + Capital);
                finLease.setCapital(Capital);
            }
            //本金增值税
            if (row.getCell(4) != null) {
                String CapitalVat = row.getCell(4).toString();
                System.out.println("本金增值税" + CapitalVat);
                finLease.setCapitalVat(CapitalVat);
            }
            //利息
            if (row.getCell(5) != null) {
                String Interest = row.getCell(5).toString();
                System.out.println("利息" + Interest);
                finLease.setInterest(Interest);
            }
            //利息增值税
            if (row.getCell(6) != null) {
                String InterestVat = row.getCell(6).toString();
                System.out.println("利息增值税" + InterestVat);
                finLease.setInterestVat(InterestVat);
            }
            //每期租金
            if (row.getCell(7) != null) {
                String LeaseAmt = row.getCell(7).toString();
                System.out.println("每期租金" + LeaseAmt);
                finLease.setLeaseAmt(LeaseAmt);
            }
            list.add(finLease);
        }
        return list;

    }

}